package es.uji.viselab.benchmark.database;

import java.awt.FlowLayout;
import java.awt.Label;
import java.awt.TextField;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.BoxLayout;
import javax.swing.JPanel;

import es.uji.viselab.benchmark.DataPackage;

public class DBConnectionMySQL extends DBConnection {

	Connection conn = null;
	JPanel rootPanel;
	protected Label labelURL = new Label("URL: ");
	protected TextField textURL = new TextField(30);

	public DBConnectionMySQL() {
		createContent();
	}

	private int getNextExperimentId() {

		if (conn == null)
			Connect();

		String sqlQuery = "SELECT max(experiment) FROM RAWDATA ";
		Statement stmt = null;
		ResultSet rs = null;
		Integer id = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sqlQuery);
			rs.first();
			if (rs != null)
				id = rs.getInt(1) + 1;
			else
				return 1; // This is the first
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException sqlEx) {
			} // ignore
			rs = null;
		}

		return id;
	}

	@Override
	public void insert(DataPackage dn) {

		if (conn == null)
			Connect();

		BMDataType dt = BMDataType.valueOf(dn.getType());
		String sqlSentence = "INSERT INTO  bmdb.RAWDATA (EXPERIMENT, DATATYPE, SEQUENCE, TIMESTAMP,VAL1,VAL2,VAL3)";

		switch (dt) {
		case XYZ_END_EFFECTOR:
			sqlSentence = sqlSentence + "VALUES ('" + experimentId + "',  '" + dt.name() + "', '"
					+ dn.getSequence() + "', CURRENT_TIMESTAMP , '" + dn.getValue(1) + "',  '"
					+ dn.getValue(2) + "', '" + dn.getValue(3) + "')";
			break;
		case ERROR:
			sqlSentence = sqlSentence + "VALUES ('" + experimentId + "',  '" + dt.name() + "', '"
					+ dn.getSequence() + "', CURRENT_TIMESTAMP , '" + dn.getValue(1) + "',  '"
					+ dn.getValue(2) + "', '0')";
			break;
		default:
			sqlSentence = null;
		}

		if (sqlSentence != null) {
			Statement stmt = null;
			try {
				stmt = conn.createStatement();
				stmt.execute(sqlSentence);
			} catch (SQLException ex) {
				// handle any errors
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException sqlEx) {
				} // ignore
				stmt = null;
			}
		}
	}

	@Override
	public void Connect() {
		try {
			conn = DriverManager.getConnection(textURL.getText());
			// Do something with the Connection
		} catch (SQLException ex) {
			// handle any errors
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}

	}

	@Override
	public double[][] getData(BMDataType dt) {

		String sqlQuery = null;
		double[][] data = null;

		switch (dt) {
		case XYZ_END_EFFECTOR:
			sqlQuery = "SELECT VAL1, VAL2, VAL3 FROM RAWDATA WHERE EXPERIMENT=" + experimentId
					+ " AND DATATYPE='XYZ_END_EFFECTOR' ORDER BY SEQUENCE ";
			break;
		case ERROR:
			sqlQuery = "SELECT VAL1, VAL2, 0 VAL3 FROM RAWDATA WHERE EXPERIMENT=" + experimentId
					+ " AND DATATYPE='ERROR' ORDER BY SEQUENCE ";
			break;
		default:
			sqlQuery = null;
		}

		if (sqlQuery != null) {
			Statement stmt = null;
			ResultSet rs = null;
			try {
				stmt = conn.createStatement();
				rs = stmt.executeQuery(sqlQuery);

				if (rs != null) {

					rs.last();
					int numResults = rs.getRow();
					data = new double[numResults][3];
					int i = 0;
					rs.first();
					while (rs.next()) {
						BigDecimal x = rs.getBigDecimal("VAL1");
						data[i][0] = x.doubleValue();
						BigDecimal y = rs.getBigDecimal("VAL2");
						data[i][1] = y.doubleValue();
						BigDecimal z = rs.getBigDecimal("VAL3");
						data[i][2] = z.doubleValue();
						i++;
					}
				}

			} catch (SQLException ex) {
				// handle any errors
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException sqlEx) {
				} // ignore
				rs = null;
			}
		}
		return data;

	}

	@Override
	public int setNewExperiment() {
		experimentId = getNextExperimentId();
		return experimentId;
	}

	@Override
	public JPanel getPanel() {
		return rootPanel;
	}

	private void createContent() {
		rootPanel = new JPanel();
		rootPanel.setLayout(new BoxLayout(rootPanel, BoxLayout.PAGE_AXIS));

		textURL.setText("jdbc:mysql://localhost/bmdb?" + "user=bmdb&password=bmdb");

		JPanel line0 = new JPanel();
		line0.setLayout(new FlowLayout());
		line0.add(labelURL);
		line0.add(textURL);
		rootPanel.add(line0);

	}

	@Override
	public String getPanelTitle() {
		return "My SQL";
	}

	@Override
	public int panelWidth() {
		return 400;
	}

	@Override
	public int panelHeight() {
		return 300;
	}
}
